package creditsProject.server.dao.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import creditsProject.dto.Credit;
import creditsProject.dto.RequestDto;
import creditsProject.dto.User;
import creditsProject.server.dao.RequestDao;
import creditsProject.server.services.impl.ManagerServiceImpl;

public class RequestDaoImpl implements RequestDao {

	final String derbyProtocol = "jdbc:derby://localhost:1527/";
	final String dbName = "BU";
	final String jdbcURL = derbyProtocol + dbName;
	final String derbyDriver = "org.apache.derby.jdbc.ClientDriver";
	static CreditDaoImpl creditDao = new CreditDaoImpl();
	static ManagerDaoImpl userDao = new ManagerDaoImpl();

	@Override
	public RequestDto getRequestById(int id) {
		return null;
	}

	@Override
	public List<RequestDto> getAllRequests() {
		List<RequestDto> res = new ArrayList<RequestDto>();

		Connection conn = null;
		try {
			System.setProperty("jdbc.drivers", derbyDriver);
			conn = DriverManager.getConnection(jdbcURL, "admin", "admin");

			PreparedStatement ps = conn
					.prepareStatement("select REQUEST_ID, CREDIT_ID, PHONE_NUMBER from APP.REQUESTS WHERE APROVED=FALSE AND DECLINED=FALSE");
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				RequestDto request = new RequestDto();
				request.setRequestId(rs.getInt("REQUEST_ID"));
				request.setCreditId(rs.getInt("CREDIT_ID"));
				request.setPhoneNumber(rs.getInt("PHONE_NUMBER"));
				res.add(request);
			}
			int crId = 0;
			Credit credit = new Credit();
			User user = new User();
			for (int i = 0; i < res.size(); i++) {
				crId = res.get(i).getCreditId();
				credit = creditDao.getCreditById(crId);
				user = userDao.getUserByPhone(res.get(i).getPhoneNumber());
				res.get(i).setUserName(user.getFio());
				res.get(i).setCreditName(credit.getName());
			}

			return res;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return res;

	}

	@Override
	public void ApproveRequests(List<Integer> id) {
		Connection conn = null;
		try {
			System.setProperty("jdbc.drivers", derbyDriver);
			conn = DriverManager.getConnection(jdbcURL, "admin", "admin");
			for (int i = 0; i < id.size(); i++) {
				PreparedStatement ps = conn
						.prepareStatement("UPDATE APP.REQUESTS SET APROVED=true WHERE REQUEST_ID=?");
				ps.setInt(1, id.get(i));
				ps.executeUpdate();
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	@Override
	public void DeclineRequests(List<Integer> id) {
		Connection conn = null;
		try {
			System.setProperty("jdbc.drivers", derbyDriver);
			conn = DriverManager.getConnection(jdbcURL, "admin", "admin");
			for (int i = 0; i < id.size(); i++) {
				PreparedStatement ps = conn
						.prepareStatement("UPDATE APP.REQUESTS SET DECLINED=true WHERE REQUEST_ID=?");
				ps.setInt(1, id.get(i));
				ps.executeUpdate();
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	@Override
	public void CreateRequests(List<Integer> ids, int phone) {
		Connection conn = null;
		System.setProperty("jdbc.drivers", derbyDriver);
		try {
			conn = DriverManager.getConnection(jdbcURL, "admin", "admin");
			for (int i = 0; i < ids.size(); i++) {
				PreparedStatement ps = conn
						.prepareStatement("select REQUEST_ID from app.REQUESTS ORDER BY REQUEST_ID DESC");
				ResultSet rs = ps.executeQuery();
				List<Integer> previuosIds = new ArrayList<>();
				while (rs.next()) {
					previuosIds.add(rs.getInt("REQUEST_ID"));
				}
				Integer previuosId = previuosIds.get(0);
				SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy kk:mm");
				String s;

				ps = conn
						.prepareStatement("insert into app.REQUESTS (REQUEST_ID, PHONE_NUMBER, CREDIT_ID, DATE_OF_REQUEST) values (?,?,?,?)");
				ps.setInt(1, previuosId + 1);
				ps.setInt(2, phone);
				ps.setInt(3, ids.get(i));
				s = sdf.format(new Date());
				ps.setString(4, s);
				ps.executeUpdate();

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	@Override
	public List<RequestDto> getAllRequestsOfUser(int phone) {
		List<RequestDto> res = new ArrayList<RequestDto>();

		Connection conn = null;
		try {
			System.setProperty("jdbc.drivers", derbyDriver);
			conn = DriverManager.getConnection(jdbcURL, "admin", "admin");

			PreparedStatement ps = conn
					.prepareStatement("select REQUEST_ID, CREDIT_ID, DATE_OF_REQUEST, APROVED, DECLINED from APP.REQUESTS WHERE PHONE_NUMBER=?");
			ps.setInt(1, phone);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				RequestDto request = new RequestDto();
				request.setRequestId(rs.getInt("REQUEST_ID"));
				request.setCreditId(rs.getInt("CREDIT_ID"));
				request.setDate(rs.getString("DATE_OF_REQUEST"));
				request.setApproved(rs.getBoolean("APROVED"));
				request.setDeclined(rs.getBoolean("DECLINED"));
				res.add(request);
			}
			int crId = 0;
			Credit credit = new Credit();
			for (int i = 0; i < res.size(); i++) {
				crId = res.get(i).getCreditId();
				//System.out.println(crId);
				credit = new Credit();
				credit =  creditDao.getCreditById(crId);
				//System.out.println(credit);
				res.get(i).setCreditName(credit.getName());
				
			}

			return res;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return res;

	}

}
